/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.viettel.bkno.visionone.dataaccess;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.viettel.bkno.visionone.entities.Role;
import com.viettel.bkno.visionone.entities.User;
import com.viettel.bkno.visionone.exception.CoreServiceException;
import com.viettel.bkno.visionone.exception.CoreServiceFault;
import com.viettel.bkno.visionone.exception.FaultType;
import com.viettel.bkno.visionone.untils.ConfigType;
import com.viettel.bkno.visionone.untils.ConfigurationManager;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;

/**
 *
 * @author hien
 */
public class DatabaseAccessor {
    Logger logger = Logger.getLogger(DatabaseAccessor.class);
    
    static DatabaseAccessor instance = new DatabaseAccessor();
    
    ComboPooledDataSource connectionPool;
    private ResultSet resultSet;

    private DatabaseAccessor() {
        try {
            connectionPool = new ComboPooledDataSource();
            connectionPool.setDriverClass(ConfigurationManager.getInstance().getConfigValue(ConfigType.DATABASE_DRIVER));
            connectionPool.setJdbcUrl(ConfigurationManager.getInstance().getConfigValue(ConfigType.DATABASE_URL));
            connectionPool.setUser(ConfigurationManager.getInstance().getConfigValue(ConfigType.DATABASE_USENAME));
            connectionPool.setPassword(ConfigurationManager.getInstance().getConfigValue(ConfigType.DATABASE_PASSWORD));
            // Thiết lập MIN, MAX CONNECTION cho ConnectionPool:
            // Số lượng connection tối thiểu là:  MIN_CONNECTION
            connectionPool.setMinPoolSize(Integer.valueOf(ConfigurationManager.getInstance().getConfigValue(ConfigType.MIN_CONNECTION)));
            // Tốc độ tạo mới connection: Mỗi lần tăng  INCREMENT_CONNECTION connection
            connectionPool.setAcquireIncrement(Integer.valueOf(ConfigurationManager.getInstance().getConfigValue(ConfigType.ACQUIRE_INCREMENT)));
            // Số lượng connection tối đa là MAX_CONNECTION
            connectionPool.setMaxPoolSize(Integer.valueOf(ConfigurationManager.getInstance().getConfigValue(ConfigType.MAX_CONNECTION)));
        } catch (PropertyVetoException | CoreServiceException ex) {
            logger.error("[NOT OK] Create connection pool to access database UNSUCCESSFUL.", ex);
        }
    }

    public static DatabaseAccessor getInstance() {
        return instance;
    }

    /**
     * Doc thong tin nguoi dung
     * @param username
     * @param password
     * @return
     * @throws CoreServiceException 
     */
    public User getUserInfo(String username, String password) throws CoreServiceException {
        User user = null;
        
        Connection connection = null;
        try {
            // Truy van database
            // Neu tim duoc user co username,password trung khop return true
            connection = connectionPool.getConnection();
        } catch (SQLException ex) {
            logger.error("Can not get connection.", ex);
            CoreServiceFault fault = new CoreServiceFault();
            fault.setFaultCode(FaultType.GET_DB_CONNECTION_EXCEPTION.getCode());
            fault.setFaultString(FaultType.GET_DB_CONNECTION_EXCEPTION.getName());
            throw new CoreServiceException(fault);
        }
        String query = "SELECT * FROM visionone.Users WHERE username = ? AND password = ?";
        try {
            java.sql.PreparedStatement prepareStm = connection.prepareStatement(query);
            prepareStm.setString(1, username);
            com.viettel.bkno.visionone.untils.SecurityManager sm = new com.viettel.bkno.visionone.untils.SecurityManager();
            prepareStm.setString(2, sm.genMd5(password));
            
            resultSet = prepareStm.executeQuery();
            
            if (resultSet.next()) {
                user = new User();
                user.setUserId(resultSet.getInt(User.USER_ID));
                user.setUsername(resultSet.getString(User.USERNAME));
                user.setRole(Role.getRole(resultSet.getInt(User.ROLE_ID)));
                user.setEmail(resultSet.getString(User.EMAIL));
                user.setTelephone(resultSet.getString(User.TELEPHONE));
                user.setFullname(resultSet.getString(User.FULL_NAME));
                user.setAddress(resultSet.getString(User.ADDRESS));
            }
            connection.close();
        } catch (SQLException ex) {
            logger.error("There are some problems when access database.", ex);
        }
        
        if (user == null) {
            CoreServiceFault fault = new CoreServiceFault();
            fault.setFaultCode(FaultType.INVALID_USER_EXCEPTION.getCode());
            fault.setFaultString(FaultType.INVALID_USER_EXCEPTION.getName());
            
            throw  new CoreServiceException(fault);
        }
        
        // Nguoc lai return false
        return user;
    }

    public void addUser(User user) throws CoreServiceException {
        String query = "INSERT INTO visionone.Users (username, password, email, role_id, full_name, address, telephone) VALUES (?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement stm = null;
        try {
            // Get connection
            conn = connectionPool.getConnection();
        } catch (SQLException ex) {
            // TODO return loi get connection
            logger.error("Can not get connection.", ex);
        }
        if (conn!= null) {
            try {
                 stm = conn.prepareStatement(query);
            } catch (SQLException ex) {
                // TODO return loi tao preparestatement
                logger.error("Can not create prepare statement.", ex);
                try {
                    conn.close();
                } catch (SQLException ex1) {
                    logger.error("Can not close connection", ex1);
                }
            }
        }
        
        if (stm != null) {
            try {
                stm.setString(1, user.getUsername());
                stm.setString(2, user.getPassword());
                stm.setString(3, user.getEmail());
                stm.setInt(4, user.getRole().getCode());
                stm.setString(5, user.getFullname());
                stm.setString(6, user.getAddress());
                stm.setString(7, user.getTelephone());
                stm.execute();
                
            } catch (SQLException ex) {
                // TODO return loi giao tiep voi database
                logger.error("Can not insert data to database.", ex);
                CoreServiceFault fault = new CoreServiceFault();
                fault.setFaultCode(FaultType.DATABASE_ACCESS_ERROR.getCode());
                fault.setFaultString(FaultType.DATABASE_ACCESS_ERROR.getName());
                throw new CoreServiceException(fault);
            }
        }
        
        
    }
}
